package com.delete.sql;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;

import org.apache.commons.lang3.StringUtils;

import com.delete.action.MAction;
import com.delete.executors.ExeParameter;
import com.kevin.enums.DBType;
import com.kevin.exceptions.CreaterException;
import com.kevin.exceptions.ExceptionFactory;
import com.kevin.extension.MFieldRow;
import com.kevin.sql.DataType;
import com.kevin.sql.SqlFormat;
import com.kevin.sql.SqlValue;
import com.kevin.table.MCellStruct;
import com.kevin.table.MCellValue;
import com.kevin.table.MDataCell;
import com.kevin.table.MDataColumn;
import com.kevin.table.MDataRow;

public class SqlCreate {

	private MAction action;

	/**
	 * 是否允许执行SQL操作（仅对Insert和Update有效；如果SQL语法错误，则拒绝执行）
	 */
	public boolean isSqlEnabled = true;

	public SqlCreate(MAction action) {
		this.action = action;
	}

	public Object[] selectColumns = null;

	// private String getTableName(){
	// return SqlFormat.keyword(action.getTableName(), action.getDalType());
	// }

	public String getMaxID() {
		switch (action.getExecutor().getDbType()) {
		case ORACLE:
			return String.format("select %s.currval from dual", "AutoID");// AutoID
																			// 未作
		default:
			return String.format("select max(%s) from %s",
					action.getStructRow().getColumns().getFirstPrimary().getColumnName(), getTableName());
		}
	}

	/**
	 * 格式化where
	 * 
	 * @param whereObj
	 * @return
	 */
	public String formatWhere(Object whereObj) {
		String where = getWhereFromObj(whereObj);
		return formatWhere(where, action.getStructRow().getColumns(), action.getDBType(),
				action.getExecutor().parametersSize() > 0);
	}

	/**
	 * 格式化where语句
	 * 
	 * @param where
	 * @param dcol
	 * @param dbType
	 * @param isFilterInjection
	 *            是否处理sql注入
	 * @return
	 */
	public static String formatWhere(String where, MDataColumn dcol, DBType dbType, boolean isFilterInjection) {
		if (StringUtils.isEmpty(where))
			return where;
		if (dcol == null || dcol.size() == 0) {
			throw new CreaterException("Column.Count=0");
		}
		where = SqlFormat.compatible(StringUtils.trim(where), dbType, isFilterInjection);
		if (dbType == DBType.MYSQL) {
			//where = SqlFormat.FormatMySqlBit(where, dcol);
		} else if (dbType == DBType.ORACLE) {
			where = SqlFormat.FormatOracleDateTime(where, dcol);
		}
		String lowerWhere = where.toLowerCase().trim();
		if (lowerWhere.startsWith("order by")) {
			where = "1=1" + where;
		} else if (StringUtils.indexOfAny(lowerWhere, '=', '>', '<') == -1 && !lowerWhere.contains(" like ")
				&& !lowerWhere.contains(" between ") && !lowerWhere.contains(" in ") && !lowerWhere.contains(" in(")
				&& !lowerWhere.contains(" is ")) {
			// 检测是否带order by 'xxxx order by xxx desc'
			int index = where.replace("\n", " ").indexOf(" order by ");// 考虑可能换行的情况xxxx\r\norder
																		// by
																		// xxx
			String orderBy = StringUtils.EMPTY;
			if (index > -1) {
				orderBy = where.substring(index + 1, where.length() - index - 1);
				where = where.substring(0, index);
			}
			if (dcol.getJointPrimary().size() > 1 && where.contains(";")) {
				// 多个主键
				StringBuffer sb = new StringBuffer();
				String[] items = StringUtils.split(where, ',');
				MDataRow row = dcol.toRow("row");
				for (int i = 0; i < items.length; i++) {
					String item = items[i];
					if (StringUtils.isNotEmpty(item)) {
						String[] values = StringUtils.split(item, ';');
						for (int j = 0; j < row.getJointPrimaryCell().size(); j++) {
							if (j < values.length) {
								row.getJointPrimaryCell().get(j).setValue(values[j]);
							}
						}
						if (i != 0) {
							sb.append(" or ");
						}
						sb.append("(" + getWhere(dbType, row.getJointPrimaryCell()) + ")");
					}
				}
				where = sb.toString();
				if (items.length == 1) {
					where = StringUtils.strip(where, "()");
				}
				items = null;
			} else {
				MCellStruct ms = dcol.getFirstPrimary();

				String[] items = where.split(",");
				if (items.length == 1) {
					// 只处理单个值的情况
					int primaryGroupID = DataType.getGroup(ms.getSqlType());// 优先匹配主键
					switch (primaryGroupID) {
					case 1:
						long val = Long.parseLong(StringUtils.trim("\'"));
						ms = dcol.getFirstUnique();
						break;
					}

					String columnName = SqlFormat.keyword(ms.getColumnName(), dbType);
					where = getWhereEqual(DataType.getGroup(ms.getSqlType()), columnName, where, dbType);
				} else {
					ArrayList<String> lists = new ArrayList<String>(items.length);
					lists.addAll(Arrays.asList(items));
					where = getWhereIn(ms, lists, dbType);
				}
			}
			if (StringUtils.isNotEmpty(orderBy)) {
				where = where + " " + orderBy;
			}
		}

		return where;
	}

	/**
	 * 根据元数据列组合where条件。
	 * 
	 * @param dbType
	 * @param cells
	 * @return
	 */
	public static String getWhere(DBType dbType, ArrayList<MDataCell> cells) {
		return getWhere(dbType, true, cells);
	}

	/**
	 * 根据元数据列组合where条件。
	 * 
	 * @param dbType
	 *            数据库类型
	 * @param isAnd
	 * @param cells
	 * @return
	 */
	public static String getWhere(DBType dbType, boolean isAnd, ArrayList<MDataCell> cells) {
		String where = "";
		MDataCell cell;
		for (int i = 0; i < cells.size(); i++) {
			cell = cells.get(i);
			if (i > 0) {
				where += (isAnd ? " and " : " or ");
			}
			int groupID = DataType.getGroup(cell.getCellstruct().getSqlType());
			String columnName = SqlFormat.keyword(cell.getColumnName(), dbType);
			switch (groupID) {
			case 1:
				where += columnName + "=" + (cell.isEmpty() ? -9999 : cell.getValue());
				break;
			case 3:
				where += columnName + "="
						+ (cell.getValue().toString().toLowerCase() == "true" ? SqlValue.TRUE : SqlValue.FALSE);
				break;
			default:
				if (groupID == 2 && dbType == DBType.ORACLE) // Oracle的日期时间要转类型
				{
					if (cell.getCellstruct().getSqlType() == Types.TIMESTAMP) {
						where += columnName + "=to_timestamp('" + cell.getStringValue()
								+ "','yyyy-MM-dd HH24:MI:ss.ff')";
					} else {
						where += columnName + "=to_date('" + cell.getStringValue() + "','yyyy-mm-dd hh24:mi:ss')";
					}
				} else {

					where += columnName + "='" + cell.getValue() + "'";
				}
				break;
			}
		}
		return where;
	}

	public static String getWhereEqual(int groupID, String columnName, String where, DBType dbType) {
		if (StringUtils.isEmpty(where)) {
			return StringUtils.EMPTY;
		}
		if (groupID != 0) {
			where = StringUtils.strip(where, "\'");
		}
		if (groupID == 1) // int 类型的，主键不为bit型。
		{
			where = columnName + "=" + where;
		} else {
			where = columnName + "='" + where + "'";
		}
		return where;
	}

	public static String getWhereIn(MCellStruct ms, ArrayList<String> items, DBType dbType) {
		if (items == null || items.size() == 0) {
			return "1=2";
		}

		StringBuffer sb = new StringBuffer();
		sb.append(SqlFormat.keyword(ms.getColumnName(), dbType));
		sb.append(" In (");
		int groupID = DataType.getGroup(ms.getSqlType());
		String item;
		for (int i = 0; i < items.size(); i++) {
			item = items.get(i);
			if (groupID != 0) {
				item = StringUtils.strip(item, "\'");// 不是字母都尝试去掉分号
			}
			if (dbType == DBType.ORACLE && i > 999 && i % 1000 == 0) // oracle
																		// 列表中的最大表达数为1000
			{
				sb.delete(sb.length() - 1, 1);// 移除最后一个,号。
				sb.append(") or " + SqlFormat.keyword(ms.getColumnName(), dbType) + " In (");
			}
			if (StringUtils.isNotEmpty(item)) {
				if (groupID == 1) {
					sb.append(item + ",");
				} else {
					if (groupID == 2 && dbType == DBType.ORACLE) {
						if (ms.getSqlType() == Types.TIMESTAMP) {
							sb.append("to_timestamp('" + item + "','yyyy-MM-dd HH24:MI:ss.ff'),");
						} else {
							sb.append("to_date('" + item + "','yyyy-mm-dd hh24:mi:ss'),");
						}
					} else {
						sb.append("'" + item + "',");
					}
				}
			}
		}
		return StringUtils.stripEnd(sb.toString(), ",") + ")";
	}

	/**
	 * where 传过来的时候是一个object 需要对其进行解析并取值
	 * 
	 * @param whereObj
	 * @return 返回相应的 where 查询语句
	 */
	private String getWhereFromObj(Object whereObj) { // 如果为空直接返回
		if (whereObj == null) {
			return StringUtils.EMPTY;
		} // 如果是字符串或者数字，直接转成字符串
		else if (whereObj instanceof String || (whereObj instanceof Number && !(whereObj instanceof Enum))) {
			return String.valueOf(whereObj);
		} // 对外开放的对象
		else if (whereObj instanceof MFieldRow) {
			MFieldRow field = (MFieldRow) whereObj;
			String fWhere = field.getSql();
			field.setSql(StringUtils.EMPTY);
			return fWhere;
		}
		// 以下是对传入的是单元格的情况(枚举也认为是单元格)
		MDataCell cell = null;
		if (whereObj instanceof Enum) {
			cell = action.getStructRow().get(((Enum<?>) whereObj).ordinal());
		} else if (whereObj instanceof MDataCell) {
			cell = (MDataCell) whereObj;
		} else {
			throw new CreaterException("the where's type is not support!");
		}
		String where = StringUtils.EMPTY;
		if (cell != null) {
			// where 条件的 单元格不能没有值
			if (cell.isEmpty()) {
				isSqlEnabled = false;
				// action.getExecutor().affectedCount = -2;
				// action.dalHelper.debugInfo.Append(AppConst.HR + "error : " +
				// cell.ColumnName + " can't be null" + AppConst.BR);
				return "1=2 and " + cell.getColumnName() + " is null";
			}
			switch (action.getExecutor().getDbType()) {
			case TXT:
			case XML:// 文本查询直接拼接字符串
				switch (DataType.getGroup(cell.getCellstruct().getSqlType())) {
				case 1:
				case 3:
					where = cell.getColumnName() + "=" + cell.getValue();
					break;
				default:
					where = cell.getColumnName() + "='" + cell.getValue() + "'";
					break;
				}
				break;
			default:// 如果是数据库查询，则执行参数化查询方法
				where = cell.getColumnName() + "=" + action.getExecutor().getPrefix() + cell.getColumnName();
				// action.executor.addParameters(cell.ColumnName, cell.Value,
				// DataType.GetDbType(cell.Struct.ValueType),
				// cell.Struct.MaxSize, ParameterDirection.Input);
				action.getExecutor()
						.addParameters(new ExeParameter(cell.getValue(), cell.getCellstruct().getSqlType()));
				break;
			}
		}
		return where;
	}

	/**
	 * 给where 语句添加排序条件
	 * 
	 * @param where
	 * @param orderColumnName
	 *            要排序的字段
	 * @return
	 */
	public static String addOrderBy(String where, String orderColumnName) {
		if (where.toLowerCase().indexOf("order by") == -1) {
			where += " order by " + orderColumnName + " asc";
		} else if (where.toLowerCase().indexOf(" asc") == -1 && where.toLowerCase().indexOf(" desc") == -1) {
			// 有order by 但没 asc
			where += " asc";
		}
		return where;
	}

	public static String removeOrderBy(String where) {
		int index = where.toLowerCase().indexOf("order by");
		if (index > 0) {
			where = where.substring(0, index);
		}
		return where;
	}

	public String getColumnsSql() {
		if (selectColumns == null || selectColumns.length == 0) {
			return "*";
		}
		String v_Columns = StringUtils.EMPTY;
		String columnName = StringUtils.EMPTY;
		for (Object column : selectColumns) {

			columnName = column.toString().trim();
			if (columnName.indexOf(' ') > -1 || columnName == "*") {
				v_Columns += columnName + ",";
			} else {
				int i = action.getStructRow().getColumns().indexOf(columnName);// 兼容字段映射
				if (i > -1) {
					v_Columns += SqlFormat.keyword(action.getStructRow().getColumns().get(i).getColumnName(),
							action.getExecutor().getDbType()) + ",";
				} else {
					// action.dbExecuter.debugInfo.Append(AppConst.HR + "warn :
					// " + TableName + " no contains column " + columnName +
					// AppConst.BR);
				}
			}
		}
		if (v_Columns == StringUtils.EMPTY) {
			return "*";
		}
		return StringUtils.stripEnd(v_Columns, ",");
	}

	public static Object sqlToViewSql(Object sqlObj) {
		if (sqlObj instanceof String) {
			String sql = String.valueOf(sqlObj).toLowerCase().trim();
			if (sql.startsWith("select ") || (sql.contains(" ") && sql.charAt(0) != '(')) {
				sqlObj = "(" + sqlObj + ") v";
			}
		}
		return sqlObj;
	}

	/**
	 * 获取主键组合的Where条件。
	 * 
	 * @return
	 */
	public String getPrimaryWhere() {
		return getWhere(action.getDBType(), action.getStructRow().getJointPrimaryCell());
	}

	// private String getEditTimeSql()
	// {
	// String editTime = AppConfig.DB.EditTimeFields;
	// if (!String.IsNullOrEmpty(editTime))
	// {
	// foreach (String item in editTime.Split(','))
	// {
	// if (!String.IsNullOrEmpty(item.Trim()))
	// {
	// if (action.Data.Columns.Contains(item) &&
	// (action.Data[item].IsNullOrEmpty || action.Data[item].State < 2))
	// {
	// return item + "='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
	// "',";//如果存在更新列
	// }
	// }
	// }
	// }
	// return String.Empty;
	// }

	private String getTableName() {
		return SqlFormat.keyword(action.getTableName(), action.getDBType());
	}

	public String getUpdateSql(Object whereObj) {
		isSqlEnabled = false;
		StringBuilder tempSql = new StringBuilder();
		tempSql.append("Update " + getTableName() + " set ");
		// if (!String.IsNullOrEmpty(updateExpression))
		// {
		// tempSql.Append(SqlCompatible.Format(updateExpression, action.DalType)
		// + ",");
		// updateExpression = null;//取完值后清除值。
		// isCanDo = true;
		// }
		// String editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。
		// if (!String.IsNullOrEmpty(editTime))
		// {
		// tempSql.append(editTime);//自带尾,号
		// }
		MDataCell cell = null;
		for (int i = 0; i < action.getStructRow().size(); i++) {
			cell = action.getStructRow().get(i);
			if (cell.getCellstruct().isPrimaryKey() || cell.getCellstruct().isAutoIncrement()) {
				continue;// 跳过自增或主键列。
			}

			if (cell.getState() > 1 && (cell.getCellstruct().isCanNull() || !cell.isNull())) {
				if (cell.getCellstruct().getSqlType() == Types.TIMESTAMP
						&& (action.getDBType() == DBType.MSSQL || action.getDBType() == DBType.SYBASE)) {
					// 更新时间戳不允许更新。
					continue;
				}
				Object value = cell.getValue();
				Class<?> valueType = cell.getCellstruct().getValueType();
				if (action.getDBType() == DBType.ORACLE && valueType == String.class
						&& cell.getStringValue().equals(StringUtils.EMPTY) && !cell.getCellstruct().isCanNull()) {
					value = " ";// Oracle not null 字段，不允许设置空值。
				}

				action.getExecutor().addParameters(new ExeParameter(value, cell.getCellstruct().getSqlType()));
				tempSql.append(SqlFormat.keyword(cell.getColumnName(), action.getDBType()) + "="
						+ action.getExecutor().getPrefix() + cell.getColumnName() + ",");
				isSqlEnabled = true;
			}
		}
		if (!isSqlEnabled) {
			// action.dalHelper.debugInfo.Append(AppConst.HR + "warn : " +
			// TableName + " can't find the data can be updated!");
		}
		tempSql = tempSql.delete(tempSql.length() - 1, tempSql.length());
		tempSql.append(" where " + formatWhere(whereObj));
		return tempSql.toString();
	}

	public String getDeleteSql(Object whereObj) {
		return "delete from " + getTableName() + " where " + formatWhere(whereObj);
	}

	// internal string GetDeleteToUpdateSql(object whereObj)
	// {
	// string editTime = GetEditTimeSql();
	// return "update " + TableName + " set " + editTime +
	// AppConfig.DB.DeleteField + "=[#TRUE] where " + FormatWhere(whereObj);
	// }

	/**
	 * 生成插入语句
	 * @return
	 */
	public String buildInsertSql(){
		return buildInsertSql(action.getStructRow());
	}
	
	/**
	 * 生成插入语句
	 * @param row
	 * @return
	 */
	public String buildInsertSql(MDataRow row) {
		StringBuilder sqlbuilder = new StringBuilder();
		StringBuilder sqlbuilder2 = new StringBuilder();
		sqlbuilder.append("insert into " + getTableName() + "(");
		sqlbuilder2.append(") Values(");

		MDataCell cell = null;
		for (int i = 0; i < row.size(); i++) {
			cell = row.get(i);
			if (cell.getCellstruct().isAutoIncrement())
				continue;
			if (cell.isNull() && !cell.getCellstruct().isCanNull() && cell.getCellstruct().getDefaultValue() == null) {
				throw ExceptionFactory.Exception(getTableName() + " : " + cell.getColumnName() + " can not be null !");
			}
			if (cell.getState() != MCellValue.NONE) {
				sqlbuilder.append(SqlFormat.keyword(cell.getColumnName(), action.getDBType()) + ",");
				sqlbuilder2.append(action.getExecutor().getPrefix() + ",");
				
				Object value = cell.getValue();
				Class<?> type = cell.getCellstruct().getValueType();
				if (action.getDBType().equals(DBType.ORACLE) && type.equals(String.class) && cell.getStringValue().equals(StringUtils.EMPTY)
						&& !cell.getCellstruct().isCanNull()) {
					value = " ";
				}
				action.setParam(new ExeParameter(value, cell.getCellstruct().getSqlType()));
			}
		}
		String sql = StringUtils.stripEnd(sqlbuilder.toString(), ",") + StringUtils.stripEnd(sqlbuilder2.toString(), ",")+ ")";
		
		return sql;
	}

	public String getInsertSql2() {
		// isCanDo = false;
		StringBuilder _TempSql = new StringBuilder();
		StringBuilder _TempSql2 = new StringBuilder();
		_TempSql.append("insert into " + getTableName() + "(");
		_TempSql2.append(") Values(");
		MDataCell primaryCell = action.getStructRow()
				.get(action.getStructRow().getColumns().getFirstPrimary().getColumnName());
		int groupID = DataType.getGroup(primaryCell.getCellstruct().getSqlType());
		String defaultValue = String.valueOf(primaryCell.getCellstruct().getDefaultValue());
		// if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 &&
		// (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36)
		// &&
		// (defaultValue == "" || defaultValue == "newid" || defaultValue ==
		// SqlValue.Guid))))//guid类型
		// {
		// primaryCell.Value = Guid.NewGuid();
		// }
		MDataCell cell = null;
		for (int i = 0; i < action.getStructRow().size(); i++) {
			cell = action.getStructRow().get(i);
			// if (cell.getCellstruct().isAutoIncrement() &&
			// !action.AllowInsertID)
			if (cell.getCellstruct().isAutoIncrement()) {
				continue;// 跳过自增列。
			}
			if (cell.isNull() && !cell.getCellstruct().isCanNull() && cell.getCellstruct().getDefaultValue() == null) {
				// action.dalHelper.debugInfo.Append(AppConst.HR +
				// String.Format("error : {0} {1} can't be null", TableName,
				// cell.ColumnName) + AppConst.BR);
				// action.getExecutor().recordsAffected = -2;
				// isCanDo = false;
				break;
			}
			if (cell.getState() > 0) {
				_TempSql.append(SqlFormat.keyword(cell.getColumnName(), action.getDBType()) + ",");
				// _TempSql2.append(action.getExecutor().getPrefix() +
				// cell.getColumnName() + ",");
				_TempSql2.append(action.getExecutor().getPrefix() + ",");
				Object value = cell.getValue();
				Class<?> type = DataType.getType(cell.getCellstruct().getSqlType(), action.getDBType());
				if (action.getDBType() == DBType.ORACLE && type == String.class && cell.getStringValue() == ""
						&& !cell.getCellstruct().isCanNull()) {
					value = " ";
				}
				// action.getExecutor().AddParameters(action.getExecutor().getPrefix()
				// + cell.getColumnName(), value, type,
				// cell.getCellstruct().MaxSize, ParameterDirection.Input);
				action.getExecutor().addParameters(new ExeParameter(value, cell.getCellstruct().getSqlType()));
				// isCanDo = true;
			}
		}
		switch (action.getExecutor().getDbType()) {
		case ORACLE:
			// if (!action.AllowInsertID &&
			// DataType.GetGroup(primaryCell.Struct.SqlType) == 1)
			if (DataType.getGroup(primaryCell.getCellstruct().getSqlType()) == 1) {
				_TempSql.append(primaryCell.getColumnName() + ",");
				// _TempSql2.Append(AutoID + ".nextval,");
			}
			break;
		}

		String sql = StringUtils.stripEnd(_TempSql.toString(), ",") + StringUtils.stripEnd(_TempSql2.toString(), ",")
				+ ")";
		switch (action.getExecutor().getDbType()) {
		case MSSQL:
		case SYBASE:
			// if (primaryCell.Struct.isAutoIncrement() && !action.AllowInsertID
			// && groupID == 1)
			if (primaryCell.getCellstruct().isAutoIncrement() && groupID == 1) {
				if (action.getExecutor().getDbType() == DBType.SYBASE) {
					sql = sql + " select @@IDENTITY as OutPutValue";
				} else {
					sql += " select cast(scope_Identity() as bigint) as OutPutValue";// 改成bigint避免转换数据溢出
				}
			} else if (!primaryCell.isEmpty()) {
				sql += String.format(" select '{0}' as OutPutValue", primaryCell.getValue());
			}
			// if (action.AllowInsertID && !action.dalHelper.isOpenTrans &&
			// primaryCell.Struct.isAutoIncrement())//非批量操作时
			if (primaryCell.getCellstruct().isAutoIncrement()) {
				sql = "set identity_insert " + SqlFormat.keyword(getTableName(), action.getExecutor().getDbType())
						+ " on " + sql + " set identity_insert "
						+ SqlFormat.keyword(getTableName(), action.getExecutor().getDbType()) + " off";
			}
			break;
		// if (!(Parent.AllowInsertID && !primaryCell.IsNull)) //
		// 对于自行插入ID的，跳过，主操作会自动返回ID。
		// {
		// sql += ((groupID == 1 && (primaryCell.IsNull ||
		// primaryCell.ToString() == "0")) ? " select cast(scope_Identity() as
		// int) as OutPutValue" : String.Format(" select '{0}' as OutPutValue",
		// primaryCell.Value));
		// }
		// case DalType.Oracle:
		// sql += String.Format("BEGIN;select {0}.currval from dual; END;",
		// AutoID);
		// break;
		default:
			break;
		}
		return sql;
	}

	public String getCountSql(Object whereObj) {
		String where = formatWhere(whereObj);
		if (StringUtils.isNotEmpty(where)) {
			where = " where " + where;
			where = removeOrderBy(where);
		}

		return "select count(*) from " + getTableName() + where;
	}
}
